﻿using Microsoft.Office.Interop.Excel;
using System;
using System.Collections.Generic;
using System.Text;

namespace Sys
{
    public class ExcelUtility
    {
        public static string ErrMsg { get; set; }
        public const int iPRINT_SHOWSCREEN = 0;
        public const int iPRINT_PRINTPREVIEW = 1;
        public const int iPRINT_PRINTIMMEDIATELY = 2;
        public static bool OpenDialogExcelFile(string strTitle, out string strFileNameOut)
        {
            strFileNameOut = "";
            bool result;
            try
            {
                System.Windows.Forms.OpenFileDialog openFileDialog = new System.Windows.Forms.OpenFileDialog();
                openFileDialog.Title = strTitle;
                openFileDialog.Filter = "Excel file (*.xls)|*.xls";
                if (openFileDialog.ShowDialog() != System.Windows.Forms.DialogResult.OK)
                {
                    result = false;
                }
                else
                {
                    strFileNameOut = openFileDialog.FileName;
                    result = true;
                }
            }
            catch (System.Exception ex)
            {
                string message = ex.Message;
                ErrMsg = message + "\r\nException from: CUtilityExcel.MyCheckAndGetTemplateFileName()";
                result = false;
            }
            return result;
        }
        public static bool OpenExcelFile(string strFileName, out Microsoft.Office.Interop.Excel.Application excelapp, out Workbook excelwb)
        {
            excelapp = null;
            excelwb = null;
            bool result;
            try
            {
                excelapp = (Microsoft.Office.Interop.Excel.Application)System.Activator.CreateInstance(System.Type.GetTypeFromCLSID(new System.Guid("00024500-0000-0000-C000-000000000046")));
                excelwb = excelapp.Workbooks.Open(strFileName, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
                result = true;
            }
            catch (System.Exception ex)
            {
                string message = ex.Message;
                ErrMsg = message + "\r\nException from: CUtilityExcel.MyOpenFile()";
                result = false;
            }
            return result;
        }
        public static bool MyOpenFileAndSaveAsResultFile(string strFileNameSource, string strFileNameResult, out Microsoft.Office.Interop.Excel.Application excelapp, out Workbook excelwb)
        {
            excelapp = null;
            excelwb = null;
            bool result;
            try
            {
                if (!OpenExcelFile(strFileNameSource, out excelapp, out excelwb))
                {
                    result = false;
                }
                else
                {
                    string directoryName = System.IO.Path.GetDirectoryName(strFileNameResult);
                    System.IO.DirectoryInfo directoryInfo = System.IO.Directory.CreateDirectory(directoryName);
                    if (directoryInfo == null)
                    {
                        result = false;
                    }
                    else
                    {
                        excelwb.SaveCopyAs(strFileNameResult);
                        excelwb.Close(false, System.Type.Missing, System.Type.Missing);
                        excelwb = excelapp.Workbooks.Open(strFileNameResult, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
                        result = true;
                    }
                }
            }
            catch (System.Exception ex)
            {
                string message = ex.Message;
                ErrMsg = message + "\r\nException from: CUtilityExcel.MyOpenFileAndSaveAsResultFile()";
                result = false;
            }
            return result;
        }
        public static bool MySaveAndShowResultFile(Microsoft.Office.Interop.Excel.Application excelapp, Workbook excelwb, int iPrintStyle)
        {
            bool result;
            try
            {
                if (excelapp == null || excelwb == null)
                {
                    result = false;
                }
                else
                {
                    excelwb.Save();
                    if (iPrintStyle == 0)
                    {
                        excelapp.Visible = true;
                    }
                    else
                    {
                        if (iPrintStyle == 1)
                        {
                            excelwb.PrintOut(System.Type.Missing, System.Type.Missing, 1, 1, 1, 0, 0, System.Type.Missing);
                        }
                        else
                        {
                            if (iPrintStyle != 2)
                            {
                                result = false;
                                return result;
                            }
                            excelwb.PrintOut(System.Type.Missing, System.Type.Missing, 1, 0, 1, 0, 0, System.Type.Missing);
                        }
                    }
                    result = true;
                }
            }
            catch (System.Exception ex)
            {
                string message = ex.Message;
                ErrMsg = message + "\r\nException from: CUtilityExcel.MyShowResultFile()";
                result = false;
            }
            return result;
        }
        public static bool MySaveAndShowResultFile(string strFileNameResult, Microsoft.Office.Interop.Excel.Application excelapp, Workbook excelwb, int iPrintStyle)
        {
            bool result;
            try
            {
                string directoryName = System.IO.Path.GetDirectoryName(strFileNameResult);
                System.IO.DirectoryInfo directoryInfo = System.IO.Directory.CreateDirectory(directoryName);
                if (directoryInfo == null)
                {
                    result = false;
                }
                else
                {
                    excelwb.SaveCopyAs(strFileNameResult);
                    excelwb.Close(false, System.Type.Missing, System.Type.Missing);
                    excelwb = excelapp.Workbooks.Open(strFileNameResult, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
                    if (!MySaveAndShowResultFile(excelapp, excelwb, iPrintStyle))
                    {
                        result = false;
                    }
                    else
                    {
                        result = true;
                    }
                }
            }
            catch (System.Exception ex)
            {
                string message = ex.Message;
                ErrMsg = message + "\r\nException from: CUtilityExcel.MySaveAndShowResultFile()";
                result = false;
            }
            return result;
        }
        public static bool MySaveAndShowResultFile(string strFileNameResult, Microsoft.Office.Interop.Excel.Application excelapp, Workbook excelwb)
        {
            return MySaveAndShowResultFile(strFileNameResult, excelapp, excelwb, 0);
        }
        public static bool MyFreeApplication(ref Microsoft.Office.Interop.Excel.Application excelapp)
        {
            bool result;
            try
            {
                if (excelapp != null)
                {
                    excelapp.Quit();
                    excelapp = null;
                }
                result = true;
            }
            catch (System.Exception ex)
            {
                string message = ex.Message;
                ErrMsg = message + "\r\nException from: CUtilityExcel.MyFreeApplication()";
                result = false;
            }
            return result;
        }
        public static System.DateTime ExcelSerialDateToDMY(int nSerialDate)
        {
            System.DateTime result;
            if (nSerialDate == 60)
            {
                int day = 29;
                int month = 2;
                int year = 1900;
                System.DateTime dateTime = new System.DateTime(year, month, day);
                result = dateTime;
            }
            else
            {
                if (nSerialDate < 60)
                {
                    nSerialDate++;
                }
                int num = nSerialDate + 68569 + 2415019;
                int num2 = 4 * num / 146097;
                num -= (146097 * num2 + 3) / 4;
                int num3 = 4000 * (num + 1) / 1461001;
                num = num - 1461 * num3 / 4 + 31;
                int num4 = 80 * num / 2447;
                int day = num - 2447 * num4 / 80;
                num = num4 / 11;
                int month = num4 + 2 - 12 * num;
                int year = 100 * (num2 - 49) + num3 + num;
                System.DateTime dateTime2 = new System.DateTime(year, month, day);
                result = dateTime2;
            }
            return result;
        }
    }
}
